/*
 * hkh-project
 * leafteamwork.blogspot.com
 * Tool: Netbeans 6.7.1 - SQL Server 2005
 */
package vn.com.hkh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;
import vn.com.hkh.dto.UserDTO;
import vn.com.hkh.util.DatabaseUtil;

/**
 *
 * @author leaf
 */
public class UserDAO {

    // Log
    private static Logger logger = Logger.getLogger(UserDAO.class.getName());
    //C: Create
    private final String SQL_CREATE = "INSERT INTO hkh_user_table(username, password, user_alias, role_id, account_id, user_when, user_status) VALUES (?,?,?,?,?,?,?)";
    //R: Read    
    private final String SQL_READ_BY_ID = "SELECT username, password, user_alias, role_id, account_id, CONVERT(varchar,user_when,103) as user_when, user_status FROM hkh_user_table WHERE username = ?";
    private final String SQL_READ_BY_ALL = "SELECT username, password, user_alias, role_id, account_id, CONVERT(varchar,user_when,103) as user_when, user_status FROM hkh_user_table ORDER BY user_when DESC";
    private final String SQL_READ_BY_STATUS = "SELECT username, password, user_alias, role_id, account_id, CONVERT(varchar,user_when,103) as user_when, user_status FROM hkh_user_table WHERE user_status = ? ORDER BY user_when DESC";
    private final String SQL_READ_FOR_AUTHENTICATION = "SELECT username, password, user_alias, role_id, account_id, CONVERT(varchar,user_when,103) as user_when, user_status FROM hkh_user_table WHERE username = ? AND password = ? AND user_status = 0";
    //U: Update
    private final String SQL_UPDATE = "UPDATE hkh_user_table SET password= ?,user_alias=?, role_id=?, account_id=?, user_when=?, user_status =? WHERE username = ?";
    private final String SQL_UPDATE_STATUS = "UPDATE hkh_user_table SET user_status =? WHERE username = ?";
    private final String SQL_UPDATE_PASSWORD = "UPDATE hkh_user_table SET password =? WHERE username = ?";
    //D: Delete
    private final String SQL_DELETE_BY_ID = "DELETE FROM hkh_user_table WHERE username = ?";

    /**
     * Create user
     * @param _userDTO
     * @return UserDTO
     */
    public UserDTO create(UserDTO _userDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_CREATE);
            _ptmt.setString(1, _userDTO.getUsername());
            _ptmt.setString(2, _userDTO.getPassword());
            _ptmt.setString(3, _userDTO.getAlias());
            _ptmt.setString(4, _userDTO.getRoleID());
            _ptmt.setString(5, _userDTO.getAccountID());
            _ptmt.setString(6, _userDTO.getWhen());
            _ptmt.setInt(7, _userDTO.getStatus());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _userDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read User by Id
     * @param _username
     * @return UserDTO
     */
    public UserDTO readById(String _username) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        UserDTO _userDTO = null;
        try {

            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_ID);
            _ptmt.setString(1, _username);

            ResultSet _rs = _ptmt.executeQuery();
            if (_rs.next()) {
                _userDTO = new UserDTO();
                _userDTO.setUsername(_rs.getString(1));
                _userDTO.setPassword(_rs.getString(2));
                 _userDTO.setAlias(_rs.getString(3));
                _userDTO.setRoleID(_rs.getString(4));
                _userDTO.setAccountID(_rs.getString(5));
                _userDTO.setWhen(_rs.getString(6));
                _userDTO.setStatus(_rs.getInt(7));

            }
            _rs.close();
            _ptmt.close();
            return _userDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read All User
     * @return Collection<UserDTO>
     */
    public Collection<UserDTO> readByAll() {
        Collection<UserDTO> _listUser = null;
        Connection _con = null;
        Statement _stmt = null;
        UserDTO _userDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _stmt = _con.createStatement();
            ResultSet _rs = _stmt.executeQuery(SQL_READ_BY_ALL);

            _listUser = new ArrayList<UserDTO>();
            while (_rs.next()) {
                _userDTO = new UserDTO();
                _userDTO.setUsername(_rs.getString(1));
                _userDTO.setPassword(_rs.getString(2));
                 _userDTO.setAlias(_rs.getString(3));
                _userDTO.setRoleID(_rs.getString(4));
                _userDTO.setAccountID(_rs.getString(5));
                _userDTO.setWhen(_rs.getString(6));
                _userDTO.setStatus(_rs.getInt(7));

                _listUser.add(_userDTO);
            }
            _rs.close();
            _stmt.close();
            return _listUser;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read User By Status
     * @param _status
     * @return
     */
    public Collection<UserDTO> readByStatus(Integer _status) {
        Collection<UserDTO> _listUser = null;
        Connection _con = null;
        PreparedStatement _ptmt = null;
        UserDTO _userDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_STATUS);
            _ptmt.setInt(1, _status);
            ResultSet _rs = _ptmt.executeQuery();
            _listUser = new ArrayList<UserDTO>();
            while (_rs.next()) {
                _userDTO = new UserDTO();
                _userDTO.setUsername(_rs.getString(1));
                _userDTO.setPassword(_rs.getString(2));
                 _userDTO.setAlias(_rs.getString(3));
                _userDTO.setRoleID(_rs.getString(4));
                _userDTO.setAccountID(_rs.getString(5));
                _userDTO.setWhen(_rs.getString(6));
                _userDTO.setStatus(_rs.getInt(7));

                _listUser.add(_userDTO);
            }
            _rs.close();
            _ptmt.close();
            return _listUser;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Authentication
     * @param _username
     * @param _password
     * @return UserDTO
     */
    public UserDTO authenticate(String _username, String _password) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        UserDTO _userDTO = null;

        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_FOR_AUTHENTICATION);
            _ptmt.setString(1, _username);
            _ptmt.setString(2, _password);

            ResultSet _rs = _ptmt.executeQuery();
            while (_rs.next()) {
                _userDTO = new UserDTO();
                 _userDTO.setUsername(_rs.getString(1));
                _userDTO.setPassword(_rs.getString(2));
                 _userDTO.setAlias(_rs.getString(3));
                _userDTO.setRoleID(_rs.getString(4));
                _userDTO.setAccountID(_rs.getString(5));
                _userDTO.setWhen(_rs.getString(6));
                _userDTO.setStatus(_rs.getInt(7));
            }
            _rs.close();
            _ptmt.close();
            return _userDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }


    /**
     * Update Users
     * @param _dto
     * @return UserDTO
     */
    public UserDTO update(UserDTO _dto) {
        Connection _con = null;
        try {
            PreparedStatement _ptmt = null;
            _con = DatabaseUtil.getConnection();

            _ptmt = _con.prepareStatement(SQL_UPDATE);
            _ptmt.setString(1, _dto.getPassword());
            _ptmt.setString(2, _dto.getAlias());
            _ptmt.setString(3, _dto.getRoleID());
            _ptmt.setString(4, _dto.getAccountID());
            _ptmt.setString(5, _dto.getWhen());
            _ptmt.setInt(6, _dto.getStatus());
            _ptmt.setString(7,_dto.getUsername());


            _ptmt.executeUpdate();
            _ptmt.close();
            return _dto;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    } // end of update

     /**
     *
     * @param _username
     * @param _newStatus
     * @return
     */
    public boolean updateStatus(String _username, Integer _newStatus) {
        Connection con = null;
        try {
            con = DatabaseUtil.getConnection();
            PreparedStatement ps = null;
            ps = con.prepareStatement(SQL_UPDATE_STATUS);
            ps.setInt(1, _newStatus);
            ps.setString(2, _username);
            ps.executeUpdate();
            ps.close();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }//end of updateStatus

     /**
     *
     * @param _username
     * @param _newPassword
     * @return
     */
    public boolean updatePassword(String _username, String _newPassword) {
        Connection con = null;
        try {
            con = DatabaseUtil.getConnection();
            PreparedStatement ps = null;
            ps = con.prepareStatement(SQL_UPDATE_PASSWORD);
            ps.setString(1, _newPassword);
            ps.setString(2, _username);
            ps.executeUpdate();
            ps.close();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }//end of updatePassword

     /**
     * Delete Users by id
     * @param _username
     * @return boolean
     */
    public boolean deleteByID(String _username) {
        Connection _con = null;
        try {

            PreparedStatement _ptmt = null;
            _con = DatabaseUtil.getConnection();

            _ptmt = _con.prepareStatement(SQL_DELETE_BY_ID);
            _ptmt.setString(1, _username);

            _ptmt.executeUpdate();
            _ptmt.close();
            return true;

        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }// end of deleteByID
}
